package dao.account;

import domain.Account;
import domain.User;
import util.DButils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class AccountDaoImpl implements AccountDao {
    /**
     * 添加支出
     * @param info
     * @param out
     * @param time
     * @param username
     * @return int
     * @author 夏添
     * @date 2020/9/23 17:26
     */
    public int addAccountOut(String info,float out,String time,String username) throws Exception{
        String sql = "insert account (info,outt,time,username) values('"+info+"',"+out+",'"+time+"','"+username+"')";
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        int i = st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }

    /**
     * 添加收入
     * @param info
     * @param come
     * @param time
     * @param username
     * @return int
     * @author 夏添
     * @date 2020/9/23 17:26
     */
    public int addAccountCome(String info,float come,String time,String username) throws Exception{
        String sql = "insert account (info,come,time,username) values('"+info+"',"+come+",'"+time+"','"+username+"')";
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        int i = st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }

    /**
     * 获取明细列表
     * @param username
     * @return List<User>
     * @author 夏添
     * @date 2020/9/23 16:03
     */
    public List<Account> getAccountList(String username) throws Exception{
        String sql="select * from account where username='"+username+"' order by id desc";
        List<Account> list= new ArrayList<>();
        Connection conn = DButils.getConn();
        Statement st =conn.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()){
            Account account = new Account();
            account.setId(rs.getInt("id"));
            account.setUsername(rs.getString("username"));
            account.setOut(rs.getFloat("outt"));
            account.setCome(rs.getFloat("come"));
            account.setInfo(rs.getString("info"));
            account.setTime(rs.getString("time"));
            list.add(account);
        }
        DButils.close(rs,st,conn);
        return list;
    }

    /**
     * 统计支出
     * @param username
     * @return int
     * @author 夏添
     * @date 2020/9/23 17:26
     */
    public float countOut(String username) throws Exception{
        String sql = "select sum(outt) from account where username='"+username+"'";
        Connection conn = DButils.getConn();
        Statement st =conn.createStatement();
        ResultSet rs = st.executeQuery(sql);
        float sumOut = 0;
        if(rs.next()){
            sumOut = rs.getFloat(1);
        }
        DButils.close(rs,st,conn);
        return sumOut;
    }

    /**
     * 统计收入
     * @param username
     * @return int
     * @author 夏添
     * @date 2020/9/23 17:26
     */
    public float countCome(String username) throws Exception{
        String sql = "select sum(come) from account where username='"+username+"'";
        Connection conn = DButils.getConn();
        Statement st =conn.createStatement();
        ResultSet rs = st.executeQuery(sql);
        float sumCome = 0;
        if(rs.next()){
            sumCome = rs.getFloat(1);
        }
        DButils.close(rs,st,conn);
        return sumCome;
    }

    /**
     * 月统计支出
     * @param username,date
     * @return float
     * @author 夏添
     * @date 2020/11/12 17:26
     */
    public float MonthCountOut(String username,String date) throws Exception{
        String sql = "select sum(outt) from account where username='"+username+"' and time like '"+date+"%'";
        Connection conn = DButils.getConn();
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sql);
        float sumOut = 0;
        if (rs.next()){
            sumOut = rs.getFloat(1);
        }
        DButils.close(rs,st,conn);
        return sumOut;
    }

    /**
     * 月统计收入
     * @param username,date
     * @return float
     * @author 夏添
     * @date 2020/11/12 17:26
     */
    public float MonthCountCome(String username,String date) throws Exception{
        String sql = "select sum(come) from account where username='"+username+"' and time like '"+date+"%'";
        Connection conn = DButils.getConn();
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sql);
        float sumCome = 0;
        if(rs.next()){
            sumCome = rs.getFloat(1);
        }
        DButils.close(rs,st,conn);
        return sumCome;
    }

}
